﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SMSModels;

namespace DAL.GoodsManager
{
    public class BorrowGoodsService
    {
        /// <summary>
        /// 获取借货表dataset
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSetFromBorrowGoodsTable()
        {
            String sql = "select 借货编号,借货单位,借货人,联系电话,借货表.货物编号,货物档案表.货物名称,借货表.仓库ID,仓库信息表.仓库名称 as 所属仓库,规格,计量单位,借货数量,经手人,借货时间,借货表.备注 from 借货表,货物档案表,仓库信息表 where 借货表.货物编号=货物档案表.货物编号 and 借货表.仓库ID=仓库信息表.仓库ID";
            String tbName = "borrowGoodsInfo";
            return SQLHelper.GetDataSet(sql, tbName);
        }


        /// <summary>
        /// 借货前判断库存中货物是否充足
        /// </summary>
        /// <param name="borrowGoods"></param>
        /// <returns>货物充足返回true，否则返回false</returns>
        public bool IsEnough(BorrowGoods borrowGoods)
        {
            String sqlIsEnough = String.Format("select 库存数量 from 库存表 where 货物编号='{0}' and 仓库ID={1}", borrowGoods.GoodsID, borrowGoods.StoreID);
            int a = Convert.ToInt32(SQLHelper.GetSingleResult(sqlIsEnough));
            if (a >= borrowGoods.BorrowGoodsQuantity)    //充足
            {
                return true;
            }
            else    //不充足
            {
                return false;
            }
        }


        /// <summary>
        /// 借货（插入借货表记录）
        /// </summary>
        /// <param name="borrowGoods"></param>
        /// <returns></returns>
        public int BorrowGoods(BorrowGoods borrowGoods)
        {
            String sql = String.Format("insert into 借货表 (借货编号,借货单位,借货人,联系电话,货物编号,仓库ID,借货数量,经手人,借货时间,备注) values('{0}','{1}','{2}','{3}','{4}',{5},{6},'{7}','{8}','{9}')", borrowGoods.BorrowGoodsID, borrowGoods.BorrowGoodsUnits, borrowGoods.Borrower, borrowGoods.BorrowerPhone, borrowGoods.GoodsID, borrowGoods.StoreID, borrowGoods.BorrowGoodsQuantity, borrowGoods.Handler, borrowGoods.BorrowGoodsTime, borrowGoods.Remarks);
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 借货时更新库存
        /// </summary>
        /// <param name="borrowGoods"></param>
        /// <returns></returns>
        public int UpdateStorageOnBorrowGoods(BorrowGoods borrowGoods)
        {
            //[1]封装SQL语句
            String sqlUpdateStorage = String.Format("UPDATE 库存表 SET 库存数量=库存表.库存数量-{0} WHERE 库存表.货物编号='{1}' and 仓库ID={2}", borrowGoods.BorrowGoodsQuantity, borrowGoods.GoodsID, borrowGoods.StoreID);
            //提交  更新库存
            return SQLHelper.Update(sqlUpdateStorage);

        }


        /// <summary>
        /// 修改借货信息
        /// </summary>
        /// <param name="borrowGoods"></param>
        /// <returns></returns>
        public int ReviseBorrowGoodsInfo(BorrowGoods borrowGoods)
        {
            //[1]封装SQL语句，修改借货信息
            String sql = String.Format("update 借货表 set 借货单位='{0}',借货人='{1}',联系电话='{2}',货物编号='{3}',仓库ID={4},借货数量={5},经手人='{6}',借货时间='{7}',备注='{8}' where 借货编号='{9}'", borrowGoods.BorrowGoodsUnits, borrowGoods.Borrower, borrowGoods.BorrowerPhone, borrowGoods.GoodsID, borrowGoods.StoreID, borrowGoods.BorrowGoodsQuantity, borrowGoods.Handler, borrowGoods.BorrowGoodsTime, borrowGoods.Remarks, borrowGoods.BorrowGoodsID);

            //[2]提交修改
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 删除借货信息
        /// </summary>
        /// <param name="borrowGoods"></param>
        /// <returns></returns>
        public int DeleteBorrowGoodsInfo(BorrowGoods borrowGoods)
        {
            //[1]封装SQL语句，删除出库信息
            String sql = String.Format("delete from 借货表 where 借货编号='{0}'", borrowGoods.BorrowGoodsID);

            //[2]提交删除
            return SQLHelper.Update(sql);

        }
    }
}
